package com.cpi.cornpp.plantinitialcost.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.corntypes.dto.CornTypesDTO;
import com.cpi.cornpp.costelement.dto.CostElementDTO;
import com.cpi.cornpp.plantinitialcost.dto.PlantInitialCostDTO;
import com.cpi.cornpp.plantinitialcost.exception.PlantInitialCostException;
import com.cpi.cornpp.plants.dto.PlantDTO;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

public class PlantInitialCostDAO extends DBDAO {
	
	private static final String SQL_PLANT_INITIAL_COST_LIST = "select  plant_initial_cost_id, plants_initials_costs.plant_id, plants.short_name, plants.description, plants_initials_costs.corn_type_id, code, corns_types.description, plants_initials_costs.cost_element_id, costs_elements.short_name, costs_elements.description, plants_initials_costs.amount, startInventory.amount from plants_initials_costs inner join plants on plants_initials_costs.plant_id = plants.plant_id inner join corns_types on plants_initials_costs.corn_type_id = corns_types.corn_type_id inner join costs_elements on plants_initials_costs.cost_element_id = costs_elements.cost_element_id inner join (select pp.corn_type_id, pv.plant_id, sum(pp.start_inventory) as amount from vw_v_plants_versions pv inner join plants_corns_types_processes_versions pp on pp.plant_version_id = pv.plant_version_id where pv.version_id = ? and pv.month_period_version_id = (SELECT MIN(mpv.month_period_version_id) FROM months_periods_versions mpv WHERE mpv.version_id = pv.version_id) group by pp.corn_type_id, pv.plant_id) startInventory on startInventory.plant_id = plants_initials_costs.plant_id and startInventory.corn_type_id = plants_initials_costs.corn_type_id where version_id = ? order by plants_initials_costs.corn_type_id, plants_initials_costs.plant_id";//$NON-NLS-1$
	private static final String SQL_PLANT_INITIAL_COST_UPDATE = "update plants_initials_costs set amount = ? where plant_initial_cost_id = ?";//$NON-NLS-1$

	public List initialCostPerVersion(int versionId)throws PlantInitialCostException{
		List plantInitialCostDTOs = null;
		PlantInitialCostDTO plantInitialCostDTO = null;
		PlantDTO plantDTO = null;
		CornTypesDTO cornTypesDTO = null;
		CostElementDTO costElementDTO = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try{
			plantInitialCostDTOs = new ArrayList();
			preparedStatement = prepareSQL(SQL_PLANT_INITIAL_COST_LIST, dsName);
			preparedStatement.setInt(1, versionId);
			preparedStatement.setInt(2, versionId);
			resultSet = executeQuery(preparedStatement);
			while(resultSet.next()){
				plantInitialCostDTO = new PlantInitialCostDTO();
				plantDTO = new PlantDTO();
				cornTypesDTO = new CornTypesDTO();
				costElementDTO = new CostElementDTO();
				plantInitialCostDTO.setVersionId(versionId);
				plantInitialCostDTO.setPlantInitialCostId(resultSet.getInt(1));
				plantDTO.setPlantId(resultSet.getInt(2));
				plantDTO.setShortName(resultSet.getString(3));
				plantDTO.setDescription(resultSet.getString(4));
				plantInitialCostDTO.setPlantDTO(plantDTO);
				cornTypesDTO.setCornTypeId(resultSet.getInt(5));
				cornTypesDTO.setCode(resultSet.getString(6));
				cornTypesDTO.setDescription(resultSet.getString(7));
				plantInitialCostDTO.setCornTypesDTO(cornTypesDTO);
				costElementDTO.setCostElementID(resultSet.getInt(8));
				costElementDTO.setCostElementCode(resultSet.getString(9));
				costElementDTO.setCostElementDescription(resultSet.getString(10));
				plantInitialCostDTO.setCostElementDTO(costElementDTO);
				plantInitialCostDTO.setAmount(resultSet.getDouble(11));
				plantInitialCostDTO.setUnloadAmount(resultSet.getDouble(12));
				plantInitialCostDTOs.add(plantInitialCostDTO);
			}
		}catch (PoolException poolException){
			PlantInitialCostException plantInitialCostException = null;
			poolException.printStackTrace(System.out);
			plantInitialCostException = new PlantInitialCostException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw plantInitialCostException;	
		} catch (SQLException sqlException) {
			PlantInitialCostException plantInitialCostException = null;
			sqlException.printStackTrace(System.out);
			plantInitialCostException = new PlantInitialCostException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw plantInitialCostException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
		return plantInitialCostDTOs;
	}
	
	public void updateInitialCost(PlantInitialCostDTO plantInitialCostDTO)throws PlantInitialCostException{
		PreparedStatement preparedStatement = null;
		try{
			preparedStatement = prepareSQL(SQL_PLANT_INITIAL_COST_UPDATE, dsName);
			preparedStatement.setDouble(1, plantInitialCostDTO.getAmount());
			preparedStatement.setInt(2, plantInitialCostDTO.getPlantInitialCostId());
			executeUpdate(preparedStatement);
		}catch (PoolException poolException){
			PlantInitialCostException plantInitialCostException = null;
			poolException.printStackTrace(System.out);
			plantInitialCostException = new PlantInitialCostException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw plantInitialCostException;	
		} catch (SQLException sqlException) {
			PlantInitialCostException plantInitialCostException = null;
			sqlException.printStackTrace(System.out);
			plantInitialCostException = new PlantInitialCostException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw plantInitialCostException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
	}
}